-- =============================================
-- Stored Procedure to get all the content records from content fodlers that do not have
-- any tags associated.
--
-- 10/11/2003   E.Tatsui   Created
-- =============================================

CREATE     PROCEDURE amsp_CMGetMissingTagContent
  @InNavMenuID numeric = NULL,
  @InContentID numeric = NULL,
  @InContactID numeric,
  @InIncludeDescendants char(1) = 'N'
AS
BEGIN
  DECLARE
    @NavMenuID numeric,
    @MaxSort numeric(28,18),
    @MinSort numeric(28,18),
    @SuperUserFlag bit

  -- Find out if this user is a member of a super group as a nav creator or editor.
  SET @SuperUserFlag = 0
  SELECT @SuperUserFlag = 1
    FROM Content_Authority_Group a WITH (NOLOCK), Content_Authority_Producer b WITH (NOLOCK)
   WHERE a.ContentAuthorityGroupID = b.ContentAuthorityGroupID
     AND b.ContactID = @InContactID
     AND (b.NavCreatorFlag = 'Y' OR b.NavEditorFlag = 'Y')
     AND a.SuperGroupFlag = 'Y'

  IF @InContentID IS NOT NULL
    SELECT @NavMenuID = NavMenuID
      FROM Content WITH (NOLOCK)
     WHERE ContentID = @InContentID

  ELSE IF @InNavMenuID IS NOT NULL AND @InIncludeDescendants  = 'Y'
    SELECT @MinSort = a.SortOrder,
           @MaxSort = (SELECT IsNull(Min(x.SortOrder),0)
              FROM Nav_Menu x
              WITH (NOLOCK)
             WHERE x.SortOrder > a.SortOrder
               AND x.CategoryDepth <= a.CategoryDepth)
      FROM Nav_Menu a  WITH (NOLOCK)
     WHERE a.NavMenuID = @InNavMenuID
ELSE
   SET @NavMenuID = @InNavMenuID

  -- Get all the content records w/o tags the user is authorized to publish.
  IF @SuperUserFlag = 1
    SELECT a.ContentID,
           a.Name,
           c.Title,
           c.NavMenuID
      FROM vCurrent_Content a WITH (NOLOCK)
      LEFT OUTER JOIN Component_Interest_Category b WITH (NOLOCK)
        ON a.ContentID = b.ComponentID
       AND b.ComponentCode = 'CM', Nav_Menu c WITH (NOLOCK)
     WHERE a.WorkflowStatusCode IN ('P','A')
       AND a.NavMenuID = c.NavMenuID
       AND c.NavContentGroupInd = 'C'
       AND b.ComponentID IS NULL
       AND ((@NavMenuID IS NOT NULL AND a.NavMenuID = @NavMenuID)
            OR (@NavMenuID IS NULL
                AND c.SortOrder >= @MinSort
                AND c.SortOrder < @MaxSort))
      ORDER BY c.SortOrder, a.SortOrder
  ELSE
    SELECT a.ContentID,
           a.Name,
           c.Title,
           c.NavMenuID
      FROM vCurrent_Content a WITH (NOLOCK)
      LEFT OUTER JOIN Component_Interest_Category b WITH (NOLOCK)
        ON a.ContentID = b.ComponentID
       AND b.ComponentCode = 'CM', Nav_Menu c WITH (NOLOCK),
           Content_Authority_Producer d
     WHERE a.WorkflowStatusCode IN ('P','A')
       AND a.NavMenuID = c.NavMenuID
       AND c.NavContentGroupInd = 'C'
       AND b.ComponentID IS NULL
       AND c.ContentAuthorityGroupID = d.ContentAuthorityGroupID
       AND d.ContactID = @InContactID
       AND (d.NavCreatorFlag = 'Y' OR d.NavEditorFlag = 'Y')
       AND ((@NavMenuID IS NOT NULL AND a.NavMenuID = @NavMenuID)
            OR (@NavMenuID IS NULL
                AND c.SortOrder >= @MinSort
                AND c.SortOrder < @MaxSort))
      ORDER BY c.SortOrder, a.SortOrder
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMGetMissingTagContent] TO [IMIS]
GO